package com.cn.ccp.service.faculty;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.bean.SpecialtyBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;

/**
 * 专业管理service
 * @author Administrator
 *
 */
public class SpecialtyService extends BasicService{
	private Log log = LogFactory.getLog(SpecialtyService.class);

	/**
	 * 创建专业
	 * @param bo
	 * @return
	 */
	public boolean saveSpecialty(SpecialtyBean bo){
		log.info(bo);
		String sql = "insert into specialty(specialtyname,description,faculty_id ) values (?,?,?)";
		return jdbcTemplate.update(sql ,new Object[]{bo.getSpecialtyname(),bo.getDescription(),bo.getFacultyId()}) > 0;
	}
	

	/**
	 * 修改专业
	 * @param bo
	 * @return
	 */
	public boolean updateSpecialty(SpecialtyBean bo){
		log.info(bo);
		String sql = "update specialty set specialtyname = ?, description = ?, faculty_id = ? where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{bo.getSpecialtyname(),bo.getDescription(),bo.getFacultyId(),bo.getId()}) > 0; 
	}
	
	public String getFacultyIdBySpecialtyID(String specialtyID){
		String sql = "SELECT faculty_id FROM specialty WHERE id = ?";
		return (String) jdbcTemplate.queryForObject(sql, new Object[]{specialtyID}, String.class);
	}
	
	public int getSpecialtyIDBySpecialtyName(String name){
		String sql = "SELECT id FROM specialty WHERE specialtyname = ?";
		return (Integer) jdbcTemplate.queryForObject(sql, new Object[]{name}, Integer.class);
	}
	
	/**
	 * 删除专业
	 * @param id
	 * @return
	 */
	public boolean delSpecialty(int id){
		String sql = "delete from specialty where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
	}
	
	public SpecialtyBean findSpecialtyById(int id) {
		String sql = "SELECT s.*,f.facultyname FROM specialty s LEFT outer JOIN faculty f ON s.faculty_id = f.id WHERE s.id = ?";
		return (SpecialtyBean) jdbcTemplate.queryForObject(sql, new Object[] { id }, new SpecialtyMapper());
	}
	
	public List queryAll(){
		String sql = "SELECT s.*,f.facultyname FROM specialty s LEFT outer JOIN faculty f ON s.faculty_id = f.id ";
		return jdbcTemplate.query(sql,new SpecialtyMapper());
	}
	
	public List pageQuerySpecialty(int start, int pagesize) {
		if(CCPContants.getUserBean().getUserName().equalsIgnoreCase("admin")){
			String sql = "SELECT s.*,f.facultyname FROM specialty s LEFT outer JOIN faculty f ON s.faculty_id = f.id  limit ?,?";
			return jdbcTemplate.query(sql, new Object[] {start,pagesize},new SpecialtyMapper()); 
		}else{
			String sql = "SELECT s.*,f.facultyname FROM specialty s LEFT outer JOIN faculty f ON s.faculty_id = f.id where faculty_id = ? limit ?,?";
			return jdbcTemplate.query(sql, new Object[] {CCPContants.getUserBean().getFacultyId(),start,pagesize},new SpecialtyMapper());
		}
	}
	
	public List pageQueryAllSpecialty() {
		String sql = "SELECT s.*,f.facultyname FROM specialty s LEFT outer JOIN faculty f ON s.faculty_id = f.id where f.id = ?";
		return jdbcTemplate.query(sql,new Object[]{CCPContants.getUserBean().getFacultyId()},new SpecialtyMapper());
	}
	
	
	public int getCountSpecialty() {
		if(CCPContants.getUserBean().getUserName().equalsIgnoreCase("admin")){
			String sql = "select count(1) from specialty ";
			return jdbcTemplate.queryForInt(sql);
		}else{
			String sql = "select count(1) from specialty where faculty_id = ? ";
			return jdbcTemplate.queryForInt(sql,new Object[]{CCPContants.getUserBean().getFacultyId()});
		}
	}
	
	/**
	 * 查看该学院下专业名是否存在
	 * @param id
	 * @param roleName
	 * @param facultyId：所在学院id
	 * @return
	 */
	public boolean specialtyNameIsAlready(int id ,String roleName,String facultyId) {
		String sql = "SELECT s.*,f.facultyname FROM specialty s LEFT outer JOIN faculty f ON s.faculty_id = f.id WHERE s.specialtyname = ? and s.faculty_id = ? and s.id != ?";
		return jdbcTemplate.query(sql, new Object[] { roleName,facultyId, id }, new SpecialtyMapper()).size() > 0;
	}
	
	public List<SpecialtyBean> getSpecialtyByFacultyId(int facultyid){
		String sql = "SELECT s.*,f.facultyname ,cm.id AS cmid FROM specialty s "+
					" LEFT OUTER JOIN course_mapping cm ON cm.other_id = s.id AND cm.course_mapping_type = 3 "+
					" LEFT OUTER JOIN faculty f ON s.faculty_id = f.id "+
					" WHERE f.id = ?";
		return jdbcTemplate.query(sql, new Object[]{facultyid}, new SpecialtyMapper());
	}
	
	
	
	public List<SpecialtyBean> getSpecialtyByFacultyIDForMapping(int facultyid,String courseId){
//		String sql = "SELECT s.*,f.facultyname ,cm.id AS cmid FROM specialty s "+
//					" LEFT OUTER JOIN course_mapping cm ON cm.other_id = s.id AND cm.course_mapping_type = 3 and cm.course_id = ?"+
//					" LEFT OUTER JOIN faculty f ON s.faculty_id = f.id "+
//					" WHERE f.id = ? ";
		StringBuffer sql = new StringBuffer();
//		sql.append("SELECT s.*,f.facultyname ,cm.id AS cmid FROM specialty s ");
//		sql.append("LEFT OUTER JOIN faculty f ON s.faculty_id = f.id");
//		sql.append("LEFT OUTER JOIN course_mapping cm ON  cm.course_id = ? and ");
//		sql.append("(");
//		sql.append(" (cm.other_id = s.id AND cm.course_mapping_type = 3)");
//		sql.append(" or (cm.other_id in (select id from grade_type where specialty_id = s.id ) AND cm.course_mapping_type = 2)	");
//		sql.append(" or (cm.other_id in (select id from user where grade in (select id from grade_type where specialty_id = s.id )) AND cm.course_mapping_type = 1) ");
//		sql.append(")");
//
//		sql.append("WHERE f.id = ? group by s.id");
		
		
		sql.append("SELECT s.*,f.facultyname ,cm.id AS cmid FROM specialty s ");
		sql.append(" LEFT OUTER JOIN faculty f ON s.faculty_id = f.id ");
		sql.append(" LEFT OUTER JOIN course_mapping cm ON  cm.course_id = ? and  ");
		sql.append(" ( ");
		sql.append(" (cm.other_id = s.id AND cm.course_mapping_type = 3) ");
		sql.append(" or (cm.other_id in (select id from grade_type where specialty_id = s.id ) AND cm.course_mapping_type = 2)	 ");
		sql.append(" or (cm.other_id in (select id from user where grade in (select id from grade_type where specialty_id = s.id )) AND cm.course_mapping_type = 1) ");
		sql.append(") ");

		sql.append("WHERE f.id = ? group by s.id ");

		return jdbcTemplate.query(sql.toString(), new Object[]{courseId,facultyid}, new SpecialtyMapper());
	}		
			
			
	
	public List<SpecialtyBean> getSpecialtyByUserId(){
		String sql = "SELECT s.*,f.facultyname "+
					" FROM specialty s "+
					" LEFT OUTER JOIN USER u ON u.faculty_id = s.faculty_id "+
					" LEFT OUTER JOIN faculty f ON f.id = s.faculty_id "+
					" WHERE u.id = "+CCPContants.getCurrUserId();
		return jdbcTemplate.query(sql, new SpecialtyMapper());
	}
	
	/**
	 * 学院封装类
	 * @author Administrator
	 *
	 */
	public class SpecialtyMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			SpecialtyBean rolesBean = new SpecialtyBean();
			rolesBean.setId(rs.getInt("id"));
			rolesBean.setSpecialtyname(rs.getString("specialtyname"));
			rolesBean.setDescription(rs.getString("description"));
			rolesBean.setFacultyName(rs.getString("facultyName"));
			rolesBean.setFacultyId(rs.getString("faculty_id"));
			try {
				String cmid = rs.getString("cmId");
				if(cmid != null  && !cmid.equals(""))
					rolesBean.setReleaseSelect(true);
			} catch (Exception e) {
				// TODO: handle exception
			}
			return rolesBean;
		}
	}
}
